package com.feng.vhr.utils;

import com.feng.vhr.pojo.*;
import com.feng.vhr.response.REmployee;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class POIUtil {
    //导出员工信息表
    public static ResponseEntity<byte[]> export(List<REmployee> employees) {
        //创建excel表格
        HSSFWorkbook empSheets = new HSSFWorkbook();
        //创建文档信息
        empSheets.createInformationProperties();
        //获取文档信息
        DocumentSummaryInformation docInfo = empSheets.getDocumentSummaryInformation();
        //设置文档信息
        //设置文档类别
        docInfo.setCategory("员工信息");
        //设置文档管理员
        docInfo.setManager("dyf");
        //设置公司信息
        docInfo.setCompany("www.dyf.cn");
        //获取文档摘要信息
        SummaryInformation sumInfo = empSheets.getSummaryInformation();
        //设置文档标题
        sumInfo.setTitle("员工信息表");
        //设置文档作者
        sumInfo.setAuthor("dyf");
        //设置文档备注
        sumInfo.setComments("vhr员工信息");
        //创建标题行样式
        HSSFCellStyle headStyle = empSheets.createCellStyle();
        //设置前景色
        headStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
        //
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //创建时间转换格式
        HSSFCellStyle dateCellStyle = empSheets.createCellStyle();
        dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
        //创建表格
        HSSFSheet sheet = empSheets.createSheet("创建员工信息表");
        //设置每列的宽度
        sheet.setColumnWidth(0, 5 * 256);
        sheet.setColumnWidth(1, 12 * 256);
        sheet.setColumnWidth(2, 10 * 256);
        sheet.setColumnWidth(3, 5 * 256);
        sheet.setColumnWidth(4, 12 * 256);
        sheet.setColumnWidth(5, 20 * 256);
        sheet.setColumnWidth(6, 10 * 256);
        sheet.setColumnWidth(7, 10 * 256);
        sheet.setColumnWidth(8, 16 * 256);
        sheet.setColumnWidth(9, 12 * 256);
        sheet.setColumnWidth(10, 15 * 256);
        sheet.setColumnWidth(11, 20 * 256);
        sheet.setColumnWidth(12, 16 * 256);
        sheet.setColumnWidth(13, 14 * 256);
        sheet.setColumnWidth(14, 14 * 256);
        sheet.setColumnWidth(15, 12 * 256);
        sheet.setColumnWidth(16, 8 * 256);
        sheet.setColumnWidth(17, 20 * 256);
        sheet.setColumnWidth(18, 20 * 256);
        sheet.setColumnWidth(19, 15 * 256);
        sheet.setColumnWidth(20, 8 * 256);
        sheet.setColumnWidth(21, 25 * 256);
        sheet.setColumnWidth(22, 14 * 256);
        sheet.setColumnWidth(23, 15 * 256);
        sheet.setColumnWidth(24, 15 * 256);

        //创建标题行
        HSSFRow r0 = sheet.createRow(0);
        //创建单元格
        HSSFCell c0 = r0.createCell(0);
        c0.setCellValue("编号");
        //设置单元格样式
        c0.setCellStyle(headStyle);

        HSSFCell c1 = r0.createCell(1);
        c1.setCellStyle(headStyle);
        c1.setCellValue("姓名");
        HSSFCell c2 = r0.createCell(2);
        c2.setCellStyle(headStyle);
        c2.setCellValue("工号");
        HSSFCell c3 = r0.createCell(3);
        c3.setCellStyle(headStyle);
        c3.setCellValue("性别");
        HSSFCell c4 = r0.createCell(4);
        c4.setCellStyle(headStyle);
        c4.setCellValue("出生日期");
        HSSFCell c5 = r0.createCell(5);
        c5.setCellStyle(headStyle);
        c5.setCellValue("身份证号码");
        HSSFCell c6 = r0.createCell(6);
        c6.setCellStyle(headStyle);
        c6.setCellValue("婚姻状况");
        HSSFCell c7 = r0.createCell(7);
        c7.setCellStyle(headStyle);
        c7.setCellValue("民族");
        HSSFCell c8 = r0.createCell(8);
        c8.setCellStyle(headStyle);
        c8.setCellValue("籍贯");
        HSSFCell c9 = r0.createCell(9);
        c9.setCellStyle(headStyle);
        c9.setCellValue("政治面貌");
        HSSFCell c10 = r0.createCell(10);
        c10.setCellStyle(headStyle);
        c10.setCellValue("电话号码");
        HSSFCell c11 = r0.createCell(11);
        c11.setCellStyle(headStyle);
        c11.setCellValue("联系地址");
        HSSFCell c12 = r0.createCell(12);
        c12.setCellStyle(headStyle);
        c12.setCellValue("所属部门");
        HSSFCell c13 = r0.createCell(13);
        c13.setCellStyle(headStyle);
        c13.setCellValue("职称");
        HSSFCell c14 = r0.createCell(14);
        c14.setCellStyle(headStyle);
        c14.setCellValue("职位");
        HSSFCell c15 = r0.createCell(15);
        c15.setCellStyle(headStyle);
        c15.setCellValue("聘用形式");
        HSSFCell c16 = r0.createCell(16);
        c16.setCellStyle(headStyle);
        c16.setCellValue("最高学历");
        HSSFCell c17 = r0.createCell(17);
        c17.setCellStyle(headStyle);
        c17.setCellValue("专业");
        HSSFCell c18 = r0.createCell(18);
        c18.setCellStyle(headStyle);
        c18.setCellValue("毕业院校");
        HSSFCell c19 = r0.createCell(19);
        c19.setCellStyle(headStyle);
        c19.setCellValue("入职日期");
        HSSFCell c20 = r0.createCell(20);
        c20.setCellStyle(headStyle);
        c20.setCellValue("在职状态");
        HSSFCell c21 = r0.createCell(21);
        c21.setCellStyle(headStyle);
        c21.setCellValue("邮箱");
        HSSFCell c22 = r0.createCell(22);
        c22.setCellStyle(headStyle);
        c22.setCellValue("合同期限(年)");
        HSSFCell c23 = r0.createCell(23);
        c23.setCellStyle(headStyle);
        c23.setCellValue("合同起始日期");
        HSSFCell c24 = r0.createCell(24);
        c24.setCellStyle(headStyle);
        c24.setCellValue("合同终止日期");
        HSSFCell c25 = r0.createCell(25);
        c25.setCellStyle(headStyle);
        c25.setCellValue("转正日期");

        for (int i = 0; i < employees.size(); i++) {
            REmployee employee = employees.get(i);
            HSSFRow row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(employee.getId());
            row.createCell(1).setCellValue(employee.getName());
            row.createCell(2).setCellValue(employee.getWorkID());
            row.createCell(3).setCellValue(employee.getGender());

            HSSFCell cell4 = row.createCell(4);
            cell4.setCellStyle(dateCellStyle);
            cell4.setCellValue(employee.getBirthday());

            row.createCell(5).setCellValue(employee.getIdCard());
            row.createCell(6).setCellValue(employee.getWedlock());
            row.createCell(7).setCellValue(employee.getNation().getName());
            row.createCell(8).setCellValue(employee.getNativePlace());
            row.createCell(9).setCellValue(employee.getPoliticsstatus().getName());
            row.createCell(10).setCellValue(employee.getPhone());
            row.createCell(11).setCellValue(employee.getAddress());
            row.createCell(12).setCellValue(employee.getDepartment().getName());
            row.createCell(13).setCellValue(employee.getJobLevel().getName());
            row.createCell(14).setCellValue(employee.getPosition().getName());
            row.createCell(15).setCellValue(employee.getEngageForm());
            row.createCell(16).setCellValue(employee.getTiptopDegree());
            row.createCell(17).setCellValue(employee.getSpecialty());
            row.createCell(18).setCellValue(employee.getSchool());
            HSSFCell cell19 = row.createCell(19);
            cell19.setCellStyle(dateCellStyle);
            cell19.setCellValue(employee.getBeginDate());
            row.createCell(20).setCellValue(employee.getWorkState());
            row.createCell(21).setCellValue(employee.getEmail());
            row.createCell(22).setCellValue(employee.getContractTerm());
            HSSFCell cell23 = row.createCell(23);
            cell23.setCellStyle(dateCellStyle);
            cell23.setCellValue(employee.getBeginContract());
            HSSFCell cell24 = row.createCell(24);
            cell24.setCellStyle(dateCellStyle);
            cell24.setCellValue(employee.getEndContract());
            HSSFCell cell25 = row.createCell(25);
            cell25.setCellStyle(dateCellStyle);
            if (employee.getConversionTime() == null) {
                cell25.setCellValue("");

            } else {
                cell25.setCellValue(employee.getConversionTime());
            }

        }
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        HttpHeaders httpHeaders = new HttpHeaders();

        try {
            httpHeaders.setContentDispositionFormData("attachment", new String("员工表.xls".getBytes("UTF-8"), "ISO-8859-1"));
            //设置返回值类型
            httpHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            empSheets.write(baos);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return new ResponseEntity<byte[]>(baos.toByteArray(), httpHeaders, HttpStatus.CREATED);
    }

    public static List<Employee> importData(MultipartFile file, List<Nation> allNations, List<Politicsstatus> allPoliticsstatus, List<Department> allDepartments, List<Position> allPositions, List<Joblevel> allJobLevels) {
        List<Employee> employees = new ArrayList<>();
        try {
            HSSFWorkbook empSheets = new HSSFWorkbook(file.getInputStream());
            //获取文件内表格数量
            int sheets = empSheets.getNumberOfSheets();
            for (int i = 0; i < sheets; i++) {
                //获取表单
                HSSFSheet sheetAt = empSheets.getSheetAt(i);
                //获取表格行数
                int physicalNumberOfRows = sheetAt.getPhysicalNumberOfRows();
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    //跳过标题行
                    if (j == 0) {
                        continue;
                    }
                    //获取行内容
                    HSSFRow row = sheetAt.getRow(j);
                    //跳过空行
                    if (row == null) {
                        continue;
                    }
                    //获取单元格数
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    Employee emp = new Employee();
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        HSSFCell cell = row.getCell(k);
                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_STRING:
                                String cellValue = cell.getStringCellValue();
                                switch (k) {
                                    case 1:
                                        emp.setName(cellValue);
                                        break;
                                    case 3:
                                        emp.setGender(cellValue);
                                        break;
                                    case 5:
                                        emp.setIdCard(cellValue);
                                        break;
                                    case 6:
                                        emp.setWedlock(cellValue);
                                        break;
                                    case 7:
                                        Nation nation = new Nation();
                                        nation.setName(cellValue);
                                        int nationIndex = allNations.indexOf(nation);
                                        emp.setNationId(allNations.get(nationIndex).getId());
                                        break;
                                    case 8:
                                        emp.setNativePlace(cellValue);
                                        break;
                                    case 9:
                                        Politicsstatus politicsstatus = new Politicsstatus();
                                        politicsstatus.setName(cellValue);
                                        int politicstatusIndex = allPoliticsstatus.indexOf(politicsstatus);
                                        emp.setPoliticId(allPoliticsstatus.get(politicstatusIndex).getId());
                                        break;
                                    case 10:
                                        emp.setPhone(cellValue);
                                        break;
                                    case 11:
                                        emp.setAddress(cellValue);
                                        break;
                                    case 12:
                                        Department department = new Department();
                                        department.setName(cellValue);
                                        int departmentIndex = allDepartments.indexOf(department);
                                        emp.setDepartmentId(allDepartments.get(departmentIndex).getId());
                                        break;
                                    case 13:
                                        Joblevel joblevel = new Joblevel();
                                        joblevel.setName(cellValue);
                                        int jobLevelIndex = allJobLevels.indexOf(joblevel);
                                        emp.setJobLevelId(allJobLevels.get(jobLevelIndex).getId());
                                        break;
                                    case 14:
                                        Position position = new Position();
                                        position.setName(cellValue);
                                        int positionIndex = allPositions.indexOf(position);
                                        emp.setPosId(allPositions.get(positionIndex).getId());
                                        break;
                                    case 15:
                                        emp.setEngageForm(cellValue);
                                        break;
                                    case 16:
                                        emp.setTiptopDegree(cellValue);
                                        break;
                                    case 17:
                                        emp.setSpecialty(cellValue);
                                        break;
                                    case 18:
                                        emp.setSchool(cellValue);
                                        break;
                                    case 20:
                                        emp.setWorkState(cellValue);
                                        break;
                                    case 21:
                                        emp.setEmail(cellValue);
                                        break;
                                }
                                break;
                            default:
                                switch (k) {
                                    case 2:
                                        int numericCellValue = (int) cell.getNumericCellValue();
                                        emp.setWorkID(String.format("%08d", numericCellValue));
                                        break;
                                    case 4:
                                        emp.setBirthday(cell.getDateCellValue());
                                        break;
                                    case 19:
                                        emp.setBeginDate(cell.getDateCellValue());
                                        break;
                                    case 23:
                                        emp.setBeginContract(cell.getDateCellValue());
                                        break;
                                    case 24:
                                        emp.setEndContract(cell.getDateCellValue());
                                        break;
                                    case 22:
                                        emp.setContractTerm(cell.getNumericCellValue());
                                        break;
                                    case 25:
                                        emp.setConversionTime(cell.getDateCellValue());
                                        break;
                                }
                                break;
                        }
                    }
                    employees.add(emp);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return employees;
    }
}
